package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pojo.Goods;
import utils.DBUtil;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public List<Goods> loadAllGoods() {
		List<Goods> list=new ArrayList<Goods>();
		String sql = "select * from goods ";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();
			while(rs.next()) {
				Goods g=new Goods();
				String gid = rs.getString("gid");
				String gname = rs.getString("gname");
				double price = rs.getDouble("price");
				int count = rs.getInt("count");
				Date gDate = new Date(rs.getDate("gDate").getTime());
				String adress  = rs.getString("address");
				
				g.setGid(gid);
				g.setGname(gname);
				g.setPrice(price);
				g.setCount(count);
				g.setgDate(gDate);
				g.setAddress(adress);
				list.add(g);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}
	
	@Override
	public List<Goods> loadAllByAdress(String address) {
		List<Goods> list=new ArrayList<Goods>();
		String sql = "select * from goods where address=?";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, address);
			rs = pst.executeQuery();
			while(rs.next()) {
				Goods g=new Goods();
				String gid = rs.getString("gid");
				String gname = rs.getString("gname");
				double price = rs.getDouble("price");
				int count = rs.getInt("count");
				Date gDate = new Date(rs.getDate("gDate").getTime());
				String adress  = rs.getString("address");
				
				g.setGid(gid);
				g.setGname(gname);
				g.setPrice(price);
				g.setCount(count);
				g.setgDate(gDate);
				g.setAddress(adress);
				list.add(g);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}
	

	@Override
	public List<Goods> loadAllOrders() {
		
		return null;
	}

	@Override
	public int findUser(String name) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Goods loadGoods(String gid) {
		Goods g=null;
		
		String sql = "select * from goods where gid=?";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, gid);;
			rs = pst.executeQuery();
			if (rs.next()) {
				
				g=new Goods();
				String gname = rs.getString("gname");
				double price = rs.getDouble("price");
				int count = rs.getInt("count");
				Date gDate = new Date(rs.getDate("gDate").getTime());
				String adress  = rs.getString("address");
				
				g.setGid(gid);
				g.setGname(gname);
				g.setPrice(price);
				g.setCount(count);
				g.setgDate(gDate);
				g.setAddress(adress);
				
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return g;
	}

	@Override
	public int addGoods(Goods g) {
		String sql = "insert into goods(gid,gname,price,count,gDate,address) values(?,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, g.getGid());
			pst.setString(2, g.getGname());
			pst.setDouble(3, g.getPrice());
			pst.setInt(4, g.getCount());
			pst.setDate(5, new java.sql.Date(g.getgDate().getTime()));
			pst.setString(6, g.getAddress());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int deleteGoods(String gid) {
		
		String sql = "delete goods where gid=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, gid);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int updateGoods(Goods g) {
		String sql = "update goods set gname=?,price=?,count=?,gDate=?,address=? where gid=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, g.getGname());
			pst.setDouble(2, g.getPrice());
			pst.setInt(3, g.getCount());
			pst.setDate(4, new java.sql.Date(g.getgDate().getTime()));
			pst.setString(5, g.getAddress());
			pst.setString(6, g.getGid());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

}
